import os
import sys
import threading
import traceback
import pythoncom
import xlwings as xw
import tkinter as tk
from tkinter import ttk, filedialog, messagebox, scrolledtext
from queue import Queue
import time
import datetime

class ExcelProcessorApp:
    def __init__(self, root):
        self.root = root
        self.root.title("Excel 数据处理工具 (线程安全版)")
        self.root.geometry("950x800")
        
        # 创建主框架
        main_frame = ttk.Frame(root, padding="10")
        main_frame.pack(fill=tk.BOTH, expand=True)
        
        # 文件选择区域
        file_frame = ttk.LabelFrame(main_frame, text="Excel 文件选择")
        file_frame.pack(fill=tk.X, pady=5)
        
        ttk.Label(file_frame, text="工作簿路径:").grid(row=0, column=0, sticky=tk.W)
        self.file_path = tk.StringVar()
        ttk.Entry(file_frame, textvariable=self.file_path, width=50).grid(row=0, column=1, padx=5)
        ttk.Button(file_frame, text="浏览...", command=self.browse_file).grid(row=0, column=2)
        
        # 参数设置区域
        params_frame = ttk.LabelFrame(main_frame, text="处理参数设置")
        params_frame.pack(fill=tk.X, pady=5)
        
        # 创建参数输入控件
        self.create_param_widgets(params_frame)
        
        # 日志区域
        log_frame = ttk.LabelFrame(main_frame, text="处理日志")
        log_frame.pack(fill=tk.BOTH, expand=True, pady=5)
        
        self.log_text = scrolledtext.ScrolledText(
            log_frame, 
            height=15,
            state=tk.DISABLED
        )
        self.log_text.pack(fill=tk.BOTH, expand=True, padx=5, pady=5)
        
        # 状态栏
        self.status_var = tk.StringVar()
        self.status_var.set("就绪")
        status_bar = ttk.Label(root, textvariable=self.status_var, relief=tk.SUNKEN, anchor=tk.W)
        status_bar.pack(side=tk.BOTTOM, fill=tk.X)
        
        # 按钮区域
        btn_frame = ttk.Frame(main_frame)
        btn_frame.pack(fill=tk.X, pady=10)
        
        ttk.Button(btn_frame, text="开始处理", command=self.start_processing).pack(side=tk.LEFT, padx=5)
        ttk.Button(btn_frame, text="停止处理", command=self.stop_processing).pack(side=tk.LEFT, padx=5)
        ttk.Button(btn_frame, text="清空日志", command=self.clear_log).pack(side=tk.LEFT, padx=5)
        ttk.Button(btn_frame, text="退出程序", command=self.exit_program).pack(side=tk.RIGHT, padx=5)
        # 线程安全控制属性
        self.task_queue = Queue()
        self.result_queue = Queue()
        self.excel_lock = threading.Lock()  # Excel操作全局锁
        self.stop_event = threading.Event()  # 线程停止信号
    
    def create_param_widgets(self, parent):
        """创建参数输入控件"""
        # 表头设置
        ttk.Label(parent, text="表头1:").grid(row=0, column=0, sticky=tk.W, padx=5, pady=2)
        self.biaotou1 = tk.StringVar(value="工作表")
        ttk.Entry(parent, textvariable=self.biaotou1, width=15).grid(row=0, column=1, sticky=tk.W, padx=5, pady=2)
        
        ttk.Label(parent, text="表头2:").grid(row=0, column=2, sticky=tk.W, padx=5, pady=2)
        self.biaotou2 = tk.StringVar(value="列名")
        ttk.Entry(parent, textvariable=self.biaotou2, width=15).grid(row=0, column=3, sticky=tk.W, padx=5, pady=2)
        
        ttk.Label(parent, text="表头3:").grid(row=0, column=4, sticky=tk.W, padx=5, pady=2)
        self.biaotou3 = tk.StringVar(value="平均值")
        ttk.Entry(parent, textvariable=self.biaotou3, width=15).grid(row=0, column=5, sticky=tk.W, padx=5, pady=2)
        
        # 可选列设置
        ttk.Label(parent, text="表头4(最大值):").grid(row=1, column=0, sticky=tk.W, padx=5, pady=2)
        self.biaotou4 = tk.StringVar(value="最大值")
        ttk.Entry(parent, textvariable=self.biaotou4, width=15).grid(row=1, column=1, sticky=tk.W, padx=5, pady=2)
        
        ttk.Label(parent, text="表头5(最小值):").grid(row=1, column=2, sticky=tk.W, padx=5, pady=2)
        self.biaotou5 = tk.StringVar(value="最小值")
        ttk.Entry(parent, textvariable=self.biaotou5, width=15).grid(row=1, column=3, sticky=tk.W, padx=5, pady=2)
        
        ttk.Label(parent, text="表头6(计数):").grid(row=1, column=4, sticky=tk.W, padx=5, pady=2)
        self.biaotou6 = tk.StringVar(value="计数")
        ttk.Entry(parent, textvariable=self.biaotou6, width=15).grid(row=1, column=5, sticky=tk.W, padx=5, pady=2)
        
        ttk.Label(parent, text="表头7(占比):").grid(row=1, column=6, sticky=tk.W, padx=5, pady=2)
        self.biaotou7 = tk.StringVar(value="大于X分占比")
        ttk.Entry(parent, textvariable=self.biaotou7, width=15).grid(row=1, column=7, sticky=tk.W, padx=5, pady=2)
        
        # 处理选项
        ttk.Label(parent, text="汇总表名称:").grid(row=2, column=0, sticky=tk.W, padx=5, pady=2)
        self.summary_sheet_name = tk.StringVar(value="汇总结果")
        ttk.Entry(parent, textvariable=self.summary_sheet_name, width=15).grid(row=2, column=1, sticky=tk.W, padx=5, pady=2)
        
        ttk.Label(parent, text="排除比例(%):").grid(row=2, column=2, sticky=tk.W, padx=5, pady=2)
        self.min_percent = tk.DoubleVar(value=5.0)
        ttk.Entry(parent, textvariable=self.min_percent, width=8).grid(row=2, column=3, sticky=tk.W, padx=5, pady=2)
        
        ttk.Label(parent, text="X分数值:").grid(row=2, column=4, sticky=tk.W, padx=5, pady=2)
        self.x_score = tk.DoubleVar(value=60.0)
        ttk.Entry(parent, textvariable=self.x_score, width=8).grid(row=2, column=5, sticky=tk.W, padx=5, pady=2)
        
        # 复选框
        self.calculate_max = tk.BooleanVar(value=True)
        ttk.Checkbutton(parent, text="计算最大值", variable=self.calculate_max).grid(row=3, column=0, sticky=tk.W, padx=5, pady=2)
        
        self.calculate_min = tk.BooleanVar(value=True)
        ttk.Checkbutton(parent, text="计算最小值", variable=self.calculate_min).grid(row=3, column=1, sticky=tk.W, padx=5, pady=2)
        
        self.calculate_count = tk.BooleanVar(value=True)
        ttk.Checkbutton(parent, text="计算计数", variable=self.calculate_count).grid(row=3, column=2, sticky=tk.W, padx=5, pady=2)
        
        self.calculate_ratio = tk.BooleanVar(value=True)
        ttk.Checkbutton(parent, text="计算大于X分占比", variable=self.calculate_ratio).grid(row=3, column=3, sticky=tk.W, padx=5, pady=2)
        
        self.exclude_zeros = tk.BooleanVar(value=True)
        ttk.Checkbutton(parent, text="排除零值", variable=self.exclude_zeros).grid(row=3, column=4, sticky=tk.W, padx=5, pady=2)
        
        self.auto_open = tk.BooleanVar(value=True)
        ttk.Checkbutton(parent, text="完成后自动打开文件", variable=self.auto_open).grid(row=3, column=5, sticky=tk.W, padx=5, pady=2)
        
        # 关键词删除
        ttk.Label(parent, text="删除关键词列(逗号分隔):").grid(row=4, column=0, sticky=tk.W, padx=5, pady=2)
        self.keywords = tk.StringVar(value="班级,年名,班名,姓名,列1")
        ttk.Entry(parent, textvariable=self.keywords, width=30).grid(row=4, column=1, columnspan=3, sticky=tk.W, padx=5, pady=2)
    
    def browse_file(self):
        """浏览并选择Excel文件"""
        file_path = filedialog.askopenfilename(
            filetypes=[("Excel文件", "*.xlsx *.xls"), ("所有文件", "*.*")]
        )
        if file_path:
            self.file_path.set(file_path)
    
    def log(self, message):
        """线程安全的日志记录方法"""
        timestamp = datetime.datetime.now().strftime("%H:%M:%S")
        formatted_message = f"[{timestamp}] {message}"
        
        # 确保在主线程操作GUI
        if threading.current_thread() != threading.main_thread():
            self.root.after(0, lambda: self._write_log(formatted_message))
        else:
            self._write_log(formatted_message)
    
    def _write_log(self, message):
        """实际写入日志的辅助方法"""
        self.log_text.config(state=tk.NORMAL)
        self.log_text.insert(tk.END, message + "\n")
        self.log_text.see(tk.END)
        self.log_text.config(state=tk.DISABLED)
        self.status_var.set(message)
    
    def clear_log(self):
        """清空日志"""
        self.log_text.config(state=tk.NORMAL)
        self.log_text.delete(1.0, tk.END)
        self.log_text.config(state=tk.DISABLED)
        self.status_var.set("日志已清空")
    
    def get_processing_params(self):
        """获取处理参数"""
        return {
            "workbook": self.file_path.get(),
            "biaotou1": self.biaotou1.get(),
            "biaotou2": self.biaotou2.get(),
            "biaotou3": self.biaotou3.get(),
            "biaotou4": self.biaotou4.get(),
            "biaotou5": self.biaotou5.get(),
            "biaotou6": self.biaotou6.get(),
            "biaotou7": self.biaotou7.get(),
            "summary_sheet_name": self.summary_sheet_name.get(),
            "min_percent": self.min_percent.get() / 100.0,  # 转换为小数
            "x_score": self.x_score.get(),
            "calculate_max": self.calculate_max.get(),
            "calculate_min": self.calculate_min.get(),
            "calculate_count": self.calculate_count.get(),
            "calculate_ratio": self.calculate_ratio.get(),
            "exclude_zeros": self.exclude_zeros.get(),
            "auto_open": self.auto_open.get(),
            "keywords_to_delete": [k.strip() for k in self.keywords.get().split(",")]
        }
    
    def validate_params(self, params):
        """验证参数有效性"""
        if not params["workbook"]:
            messagebox.showerror("错误", "请选择Excel文件")
            return False
        
        if not os.path.exists(params["workbook"]):
            messagebox.showerror("错误", "选择的文件不存在")
            return False
        
        if params["min_percent"] < 0 or params["min_percent"] > 100:
            messagebox.showerror("错误", "排除比例必须在0-100之间")
            return False
        
        return True
    
    def start_processing(self):
        """启动线程安全的处理流程"""
        params = self.get_processing_params()
        
        # 验证参数
        if not self.validate_params(params):
            return
        
        # 重置停止事件
        self.stop_event.clear()
        
        # 清空日志
        self.clear_log()
        self.log("开始线程安全的数据处理...")
        
        # 创建并启动工作线程
        self.worker_thread = threading.Thread(
            target=self.safe_excel_processing, 
            args=(params,),
            daemon=True
        )
        self.worker_thread.start()
    
    def safe_excel_processing(self, params):
        """线程安全的Excel处理流程"""
        pythoncom.CoInitialize()
        try:
            # === 关键修改1：使用独立Excel实例 ===
            with self.excel_lock:  # 确保串行创建实例
                app = xw.App(visible=False)
                wb = app.books.open(params["workbook"])
            
            # 存储所有工作表的汇总结果
            all_results = []
            
            # 遍历所有工作表
            for sheet in wb.sheets:
                if self.stop_event.is_set():
                    self.log("处理已停止")
                    break
                    
                sheet_name = sheet.name
                if sheet_name == params["summary_sheet_name"]:
                    continue
                
                self.log(f"安全处理工作表: {sheet_name}")
                
                # 获取数据范围
                last_cell = sheet.used_range.last_cell
                nrows, ncols = last_cell.row, last_cell.column
                
                # 获取表头
                headers = []
                for col in range(1, ncols + 1):
                    header = sheet.range((1, col)).value
                    headers.append(header if header else f"列{col}")
                
                # === 关键修改2：使用线程安全的数据处理 ===
                results = self.thread_safe_column_processing(
                    sheet, nrows, ncols, params, headers
                )
                
                # 收集结果
                for i, (header, result) in enumerate(zip(headers, results)):
                    result_data = {
                        params["biaotou1"]: sheet_name,
                        params["biaotou2"]: header,
                        params["biaotou3"]: result["avg"]
                    }
                    
                    # 动态添加可选列
                    if params["calculate_max"]:
                        result_data[params["biaotou4"]] = result["max"]
                    if params["calculate_min"]:
                        result_data[params["biaotou5"]] = result["min"]
                    if params["calculate_count"]:
                        result_data[params["biaotou6"]] = result["count"]
                    if params["calculate_ratio"]:
                        result_data[params["biaotou7"]] = result["ratio"]
                    
                    all_results.append(result_data)
            
            # === 关键修改3：安全写入汇总表 ===
            self.safe_write_summary(wb, params, headers, all_results)
            
            # 保存工作簿
            wb.save()
            self.log(f"处理完成！共处理 {len(all_results)} 条数据")
            
            if params["auto_open"]:
                self.log("正在打开Excel文件...")
                os.startfile(params["workbook"])
        
        except pythoncom.com_error as e:
            # 专门捕获COM错误
            if e.hresult == -2147417848:
                self.log("安全处理：忽略已释放的COM对象访问")
            else:
                self.log(f"COM错误: {e.excepinfo[2]}")
        except Exception as e:
            self.log(f"处理过程中发生错误: {str(e)}")
            self.log(traceback.format_exc())
        finally:
            # === 关键修改4：安全的资源释放 ===
            try:
                if 'wb' in locals(): 
                    wb.close()
                if 'app' in locals(): 
                    app.quit()
            except pythoncom.com_error:
                self.log("安全释放资源时忽略COM错误")
            finally:
                pythoncom.CoUninitialize()
    
    def thread_safe_column_processing(self, sheet, nrows, ncols, params, headers):
        """线程安全的列数据处理"""
        results = []
        for col in range(1, ncols + 1):
            if self.stop_event.is_set():
                break
                
            try:
                # 获取列数据
                col_data = sheet.range((2, col), (nrows, col)).value
                
                # 筛选数值数据
                if params["exclude_zeros"]:
                    numeric_data = [x for x in col_data 
                                  if isinstance(x, (int, float)) and x is not None and x != 0]
                else:
                    numeric_data = [x for x in col_data 
                                  if isinstance(x, (int, float)) and x is not None]
                
                # 计算统计指标
                result = self.calculate_statistics(numeric_data, params)
                results.append(result)
                
                # 记录进度
                if col % 5 == 0 or col == ncols:
                    self.log(f"处理列 {col}/{ncols}: {headers[col-1]}")
                
            except Exception as e:
                self.log(f"列 {col} 处理错误: {str(e)}")
                results.append({
                    "avg": None,
                    "max": None,
                    "min": None,
                    "count": 0,
                    "ratio": None
                })
        return results
    
    def calculate_statistics(self, numeric_data, params):
        """计算统计指标（线程安全）"""
        count = len(numeric_data)
        
        # 计算大于X分占比
        above_x_count = 0
        if params["calculate_ratio"] and count > 0:
            above_x_count = sum(1 for x in numeric_data if x > params["x_score"])
            ratio = above_x_count / count
        else:
            ratio = None
        
        if count > 0:
            # 计算排除数量
            exclude_count = max(1, int(count * params["min_percent"]) + 1)
            sorted_data = sorted(numeric_data)
            
            # 确保不超出范围
            if exclude_count >= len(sorted_data):
                trimmed_data = sorted_data
            else:
                trimmed_data = sorted_data[exclude_count:]
            
            # 计算平均值
            avg = sum(trimmed_data) / len(trimmed_data) if trimmed_data else 0
            
            # 计算最高分
            max_score = max(numeric_data) if params["calculate_max"] and numeric_data else None
            
            # 计算最低分
            min_score = min(numeric_data) if params["calculate_min"] and numeric_data else None
            
            return {
                "avg": avg,
                "max": max_score,
                "min": min_score,
                "count": count,
                "ratio": ratio
            }
        else:
            return {
                "avg": None,
                "max": None,
                "min": None,
                "count": 0,
                "ratio": None
            }
    
    def safe_write_summary(self, wb, params, headers, all_results):
        """线程安全的汇总表写入"""
        # 创建或清除汇总工作表
        if params["summary_sheet_name"] in [s.name for s in wb.sheets]:
            summary_sheet = wb.sheets[params["summary_sheet_name"]]
            summary_sheet.clear()
            self.log(f"已清除汇总工作表: {params['summary_sheet_name']}")
        else:
            summary_sheet = wb.sheets.add(params["summary_sheet_name"], after=wb.sheets[-1])
            self.log(f"已创建汇总工作表: {params['summary_sheet_name']}")
        
        # 确定要写入的列标题
        full_headers = self.get_full_headers(params)
        
        # 写入汇总表头
        summary_sheet.range("A1").value = [full_headers]
        summary_sheet.range(f"A1:{chr(64 + len(full_headers))}1").api.Font.Bold = True
        
        # 写入汇总数据
        for i, result in enumerate(all_results):
            row_data = [
                result[params["biaotou1"]],
                result[params["biaotou2"]],
                result[params["biaotou3"]]
            ]
            
            # 动态添加可选列
            if params["calculate_max"]:
                row_data.append(result.get(params["biaotou4"], ""))
            if params["calculate_min"]:
                row_data.append(result.get(params["biaotou5"], ""))
            if params["calculate_count"]:
                row_data.append(result.get(params["biaotou6"], ""))
            if params["calculate_ratio"]:
                row_data.append(result.get(params["biaotou7"], ""))
            
            summary_sheet.range(f"A{i + 2}").value = row_data
        
        # 优化格式
        self.format_summary_sheet(summary_sheet, params, full_headers)
        
        # 删除包含关键词的行
        self.log("正在安全删除包含关键词的行...")
        self.safe_delete_keyword_rows(summary_sheet, params)
    
    def safe_delete_keyword_rows(self, sheet, params):
        """线程安全的关键词行删除"""
        last_row = sheet.range('B' + str(sheet.cells.last_cell.row)).end('up').row
        deleted_count = 0
        
        # 使用COM安全操作
        try:
            for row in range(last_row, 1, -1):
                if self.stop_event.is_set():
                    break
                    
                cell_value = sheet.range(f'B{row}').value
                if cell_value in params["keywords_to_delete"]:
                    # 使用API删除行
                    sheet.range(f'B{row}').api.EntireRow.Delete()
                    deleted_count += 1
                    
                    # 添加延迟避免COM过载
                    time.sleep(0.01)
        except pythoncom.com_error as e:
            if e.hresult != -2147417848:  # 忽略已知断开错误
                raise
                
        self.log(f"已安全删除 {deleted_count} 行")
    
    def get_full_headers(self, params):
        """获取完整的表头列表"""
        headers = [
            params["biaotou1"], 
            params["biaotou2"], 
            params["biaotou3"]
        ]
        
        if params["calculate_max"]:
            headers.append(params["biaotou4"])
        if params["calculate_min"]:
            headers.append(params["biaotou5"])
        if params["calculate_count"]:
            headers.append(params["biaotou6"])
        if params["calculate_ratio"]:
            headers.append(params["biaotou7"])
            
        return headers
    
    def format_summary_sheet(self, sheet, params, headers):
        """设置汇总表格式"""
        # 自动调整列宽
        sheet.range(f"A:{chr(64 + len(headers))}").api.Columns.AutoFit()
        
        # 设置数字格式
        avg_col = chr(64 + headers.index(params["biaotou3"]) + 1)
        sheet.range(f"{avg_col}:{avg_col}").api.NumberFormat = "0.00"
        
        if params["calculate_max"]:
            max_col = chr(64 + headers.index(params["biaotou4"]) + 1)
            sheet.range(f"{max_col}:{max_col}").api.NumberFormat = "0"
        
        if params["calculate_min"]:
            min_col = chr(64 + headers.index(params["biaotou5"]) + 1)
            sheet.range(f"{min_col}:{min_col}").api.NumberFormat = "0"
        
        # 设置占比列为百分比格式
        if params["calculate_ratio"]:
            ratio_col = chr(64 + headers.index(params["biaotou7"]) + 1)
            sheet.range(f"{ratio_col}:{ratio_col}").api.NumberFormat = "0.00%"
    
    def stop_processing(self):
        """停止处理过程"""
        self.stop_event.set()
        if hasattr(self, 'worker_thread') and self.worker_thread.is_alive():
            self.worker_thread.join(timeout=5.0)
        self.log("处理已停止")

    def exit_program(self):
        """安全退出程序"""
        # 设置停止事件通知所有线程
        self.stop_event.set()

        # 等待所有线程结束（最多等待5秒）
        self.log("正在停止工作线程...")
        start_time = time.time()
        while threading.active_count() > 1:  # 主线程除外
            if time.time() - start_time > 5:
                self.log("警告：部分线程未正常退出，强制终止")
                break
            time.sleep(0.1)

        # 关闭主窗口
        self.root.destroy()

        # 完全退出程序
        sys.exit(0)

if __name__ == "__main__":
    root = tk.Tk()
    app = ExcelProcessorApp(root)
    root.mainloop()
